#delim ;
set more off;
capture log close;
capture clear;
*log using , text replace;
set memory 200m;

/***!***!***!***!***!*** [National_banks_combine.do ] ***!***!***!***!***!
*
* Project: National Banks  		
* Programmer:  Scott Fulford
*
* Date:    	 12/12/12
*
* Auditor:      
* Audit Date:   
*
* Purpose:      
* 1) Combine aggregate data for post 1900 from MT  and automatic checks to create final data
* 2) 
* 3)
* Inputs: compcurr_1910_aggregate_assets.xls 
	
*
* Ouputs: 
*		
*
*
***!***!***!***!***!***!***!***!***!***!***!***!***!***!***!***!***/

pause on;
/***Define Global Directory ****/
	local INDIR "C:\Scott\Research\National_Banks\Crisis_intermediate";
	local PROGDIR  "C:\Scott\Research\National_Banks\Crisis_progs";	
/*******************************/

/* Load Assets by copying from compcurr_1910_aggregate_assets.xls 
foreach thisyear in 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910  {;
	clear all;
	display "Copy in `thisyear'";
	edit;
	pause;
	sxpose, clear firstnames;
	gen call_year = `thisyear';
	tempfile asset`thisyear';
	save `asset`thisyear'', replace;
};	


/*Put all of the files together*/
use `asset1900', clear;
foreach thisyear in  1901 1902 1903 1904 1905 1906 1907 1908 1909 1910  {;
	append using `asset`thisyear'';
};

sort call_year call_date ;
cd "`INDIR'";
save NB_assets_1900_1910, replace;
*/

/*Load liabilities by copying from compcurr_1910_aggregate_liabilities.xls
foreach thisyear in 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910  {;
	clear all;
	display "Copy in `thisyear'";
	edit;
	pause;
	sxpose, clear firstnames;
	gen call_year = `thisyear';
	tempfile liab`thisyear';
	save `liab`thisyear'', replace;
};	


/*Put all of the files together*/
use `liab1900', clear;
foreach thisyear in  1901 1902 1903 1904 1905 1906 1907 1908 1909 1910  {;
	append using `liab`thisyear'';
};

sort call_year call_date ;
cd "`INDIR'";
save NB_liab_1900_1910, replace;

exit;
*/
cd "`INDIR'";
use NB_liab_1900_1910, clear;
ds call_year call_date total_liabilities banks, not;

foreach var in `r(varlist)' {;
	rename `var' `var'_liab;
};
replace call_date = itrim(call_date);
sort call_year call_date;
tempfile liab;
save `liab', replace;

cd "`INDIR'";
use NB_assets_1900_1910, clear;
ds call_year call_date total_assets banks, not;

foreach var in `r(varlist)' {;
	rename `var' `var'_assets;
};
sort call_year call_date;
tempfile assets;
save `assets', replace;

merge 1:1 call_year call_date using `liab';
drop _merge;


ds call_year call_date , not;
tempvar temp_var;
foreach var in `r(varlist)' {;
	tempvar temp_var;
	replace `var' = subinstr( `var', ",", "" ,.);
	gen `temp_var' = real(`var');
	drop `var';
	rename `temp_var' `var';
};

tempfile all;
save `all', replace;

replace call_date = subinstr(call_date,".","",.);
gen d1 = call_date + ", "+  string(call_year); 
gen cd1 = date(d1, "MDY") ;
format cd1 %td;
gen month1 = month(cd1);
gen day1 = day(cd1);
rename call_date orig_call_date;
gen call_date = mdy(month1, day1, call_year);
format call_date %td;
label var call_date "Call date";
label var call_year "Call year";

/*Drop variables created in making data set*/
drop cd1 month1 day1 d1  orig_call_date;


/*Rename some inconsistently named variables*/
rename due_trust_liab_liab due_trust_liab;
rename other_liabilities_liab other_liab;
rename total_liabilities total_liab;

cd "`INDIR'";
save NB_aggregate_1900_1910, replace;

/*Check the data*/
tsset call_date;
twoway (tsline banks), name(banks, replace);
twoway (tsline total_assets), name(total_assets, replace);
twoway (tsline total_liab), name(total_liab, replace);


exit;